﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
using System.Data.Sql;
using DAO;

/// <summary>
/// Summary description for ThemProductDAO
/// </summary>
public class ProductDAO
{
    public ProductDAO()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    #region Lay thong tin
    public static DataTable LayComments()
    {
        string sQuery = "select * from Product where Comments <> '' ";
        SqlConnection con = DataProvider.TaoKetNoi();
        DataTable dt = DataProvider.LayDataTable(sQuery, con);
        DataProvider.DongKetNoi(con);
        return dt;
    }
    public static bool KiemTraProductTonTai(ProductDTO spDTO)
    {
        string sQuery = "select * from Product where ProductCode=" + spDTO.Ma;
        SqlConnection con = DataProvider.TaoKetNoi();
        DataTable dt = DataProvider.LayDataTable(sQuery, con);
        DataProvider.DongKetNoi(con);
        if (dt.Rows.Count > 0)
        {
            return true;
        }
        return false;
    }
    public static string CommentsCu(ProductDTO spDTO)
    {
        string CommentsCu;
        string sQuery = "select Comments from Product where ProductCode = " + spDTO.Ma;
        SqlConnection con = DataProvider.TaoKetNoi();
        DataTable dt = DataProvider.LayDataTable(sQuery, con);
        DataProvider.DongKetNoi(con);
        if (dt == null)
        {
            CommentsCu = "";
        }
        else
            CommentsCu = dt.Rows[0][0].ToString();
        return CommentsCu;
    }
    public static DataTable LayDanhSachProduct()
    {
        string sQuery = "select * from Product order by ProductCode asc";
        SqlConnection con = DataProvider.TaoKetNoi();
        DataTable dt = DataProvider.LayDataTable(sQuery, con);
        DataProvider.DongKetNoi(con);
        return dt;
    }
    public static DataTable LayThongTinProduct(ProductDTO spDTO)
    {
        string sQuery = "";
        if (spDTO.ProductType == 0 && spDTO.Company != null && spDTO.Key == null && spDTO.GiaLon == -1)
            sQuery = "select * from Product where Company ='" + spDTO.Company + "' and Active = 1 order by EnterEveryDay DESC";
        if (spDTO.ProductType != 0 && spDTO.Company == null && spDTO.Key == null && spDTO.GiaLon == -1)
            sQuery = "select * from Product where ProductType =" + spDTO.ProductType + " and Active = 1 order by EnterEveryDay DESC";
        if (spDTO.ProductType != 0 && spDTO.Company != null && spDTO.Key == null && spDTO.GiaLon == -1)
            sQuery = "select * from Product where ProductType =" + spDTO.ProductType + " and Company ='" + spDTO.Company + "' and Active = 1 order by EnterEveryDay DESC";
        if (spDTO.ProductType == 0 && spDTO.Company == null && spDTO.Key != null && spDTO.GiaLon == -1)
            sQuery = "select * from Product where ProductName like '%" + spDTO.Key + "%' or Company like '%"
                + spDTO.Key + "%' or UnitPrice like '%" + spDTO.Key + "%' and Active = 1 order by EnterEveryDay DESC";
        if (spDTO.ProductType == 0 && spDTO.Company == null && spDTO.Key == null && spDTO.GiaLon == -1)
            sQuery = "select * from Product where Active = 1 ORDER BY EnterEveryDay DESC";
        if (spDTO.GiaNho >= 0 && spDTO.GiaNho > 0)
            sQuery = "select * from Product where UnitPrice >=" + spDTO.GiaNho + " and Active = 1  and UnitPrice <=" + spDTO.GiaLon;
        SqlConnection con = DataProvider.TaoKetNoi();
        DataTable dt = DataProvider.LayDataTable(sQuery, con);
        DataProvider.DongKetNoi(con);
        return dt;
    }
    #endregion
    #region Them thong tin
    public static bool ThemProduct(ProductDTO spDTO)
    {
        string sQuery = "insert into Product(ProductName,Description,Avatar,UnitPrice,LongerNumber,ProductType,Company,LargeImage,AverageFigure,EnterEveryDay,LikeNumber,Active) values('" 
            + spDTO.Ten + "','"+ spDTO.Description+"','" + spDTO.DuongDanLogo + "'," + spDTO.UnitPrice + "," 
            + spDTO.LongerNumber + "," + spDTO.ProductType + ",'" + spDTO.Company + "','" + spDTO.LargeImage + "','" + spDTO.AverageFigure 
            +  "','"+spDTO.EnterEveryDay+"',0,1)";
        SqlConnection con = DataProvider.TaoKetNoi();
        bool ketqua = DataProvider.ExecuteNonQuery(sQuery, con);
        DataProvider.DongKetNoi(con);
        return ketqua;
    }
    public static bool TangLike(ProductDTO spDTO)
    {
        string sQuery = "update Product set LikeNumber = " + spDTO.LikeNumber + " where ProductCode =" + spDTO.Ma;
        SqlConnection con = DataProvider.TaoKetNoi();
        bool ketqua = DataProvider.ExecuteNonQuery(sQuery, con);
        DataProvider.DongKetNoi(con);
        return ketqua;
    }

   
    public static bool ThemComments(ProductDTO spDTO)
    {
        string sQuery;
        if(CommentsCu(spDTO) == "")
            sQuery = "update Product set Comments = '" +spDTO.Comments + "' where ProductCode =" + spDTO.Ma;
        else
            sQuery = "update Product set Comments = '"+ spDTO.NgayComments + " " + spDTO.NguoiComments + " " 
                +spDTO.Comments +"\r\n"+ CommentsCu(spDTO) 
                + "' where ProductCode =" + spDTO.Ma;
        SqlConnection con = DataProvider.TaoKetNoi();
        bool ketqua = DataProvider.ExecuteNonQuery(sQuery, con);
        DataProvider.DongKetNoi(con);
        return ketqua;
    }

    public static void EditInfo(ProductDTO spDTO)
    { 
        string sQuery = "update Product set ProductName ='"+spDTO.Ten+"',Avatar = '"+spDTO.DuongDanLogo
            +"',UnitPrice ="+spDTO.UnitPrice+",LongerNumber="+spDTO.LongerNumber+",ProductType="+spDTO.ProductType+",Company ='"+spDTO.Company
            +"',LikeNumber ="+spDTO.LikeNumber+",EnterEveryDay = '"+spDTO.EnterEveryDay+"' where ProductCode = " +spDTO.Ma;
        SqlConnection con = DataProvider.TaoKetNoi();
        DataProvider.ExecuteNonQuery(sQuery, con);
        DataProvider.DongKetNoi(con);
    }
    public static void SetOnOff(ProductDTO spDTO)
    {
        string sQuery = "update Product set Active = "+spDTO.Active+" where ProductCode =" + spDTO.Ma;
        SqlConnection con = DataProvider.TaoKetNoi();
        DataProvider.ExecuteNonQuery(sQuery, con);
        DataProvider.DongKetNoi(con);
    }
    public static void XoaProduct(ProductDTO spDTO)
    {
        string sQuery = "delete from Product where ProductCode =" + spDTO.Ma;
        SqlConnection con = DataProvider.TaoKetNoi();
        DataProvider.ExecuteNonQuery(sQuery, con);
        DataProvider.DongKetNoi(con);
    }
    public static DataTable Search(ProductDTO spDTO)
    {
        string sQuery = "";
        if(spDTO.GiaNho != 0 && spDTO.GiaLon != 0 && spDTO.Ten != null)
            sQuery = "select * from Product where ProductName like '%" + spDTO.Ten + "%' and Company ='" + spDTO.Company
            + "' and ProductType = " + spDTO.ProductType + " and UnitPrice >=" + spDTO.GiaNho + " and UnitPrice <=" + spDTO.GiaLon + " and Active = 1 order by EnterEveryDay DESC";
        if (spDTO.GiaNho != 0 && spDTO.GiaLon != 0 && spDTO.Ten == null)
            sQuery = "select * from Product where Company ='" + spDTO.Company + "' and ProductType = " + spDTO.ProductType + " and UnitPrice >="
                + spDTO.GiaNho + " and UnitPrice <=" + spDTO.GiaLon + " and Active = 1 order by EnterEveryDay DESC";
        if (spDTO.GiaNho != 0 && spDTO.GiaLon == 0 && spDTO.Ten != null)
            sQuery = "select * from Product where ProductName like '%" + spDTO.Ten + "%' and Company ='" + spDTO.Company
            + "' and ProductType = " + spDTO.ProductType + " and UnitPrice >=" + spDTO.GiaNho + " and Active = 1 order by EnterEveryDay DESC";
        if (spDTO.GiaNho == 0 && spDTO.GiaLon != 0 && spDTO.Ten != null)
            sQuery = "select * from Product where ProductName like '%" + spDTO.Ten + "%' and Company ='" + spDTO.Company
            + "' and ProductType = " + spDTO.ProductType + " and UnitPrice <=" + spDTO.GiaLon + " and Active = 1 order by EnterEveryDay DESC";
        if (spDTO.GiaNho != 0 && spDTO.GiaLon == 0 && spDTO.Ten == null)
            sQuery = "select * from Product where Company ='" + spDTO.Company + "' and ProductType = " + spDTO.ProductType + " and UnitPrice >="
                + spDTO.GiaNho + " and Active = 1 order by EnterEveryDay DESC";
        if (spDTO.GiaNho == 0 && spDTO.GiaLon != 0 && spDTO.Ten == null)
            sQuery = "select * from Product where Company ='" + spDTO.Company + "' and ProductType = " + spDTO.ProductType + " and UnitPrice <="
                + spDTO.GiaLon + " and Active = 1 order by EnterEveryDay DESC";
        if (spDTO.GiaNho == 0 && spDTO.GiaLon == 0 && spDTO.Ten != null)
            sQuery = "select * from Product where ProductName like '%" + spDTO.Ten + "%' and Company ='" + spDTO.Company
            + "' and Active = 1 and ProductType = " + spDTO.ProductType;
        if (spDTO.GiaNho == 0 && spDTO.GiaLon == 0 && spDTO.Ten == null)
            sQuery = "select * from Product where Company ='" + spDTO.Company + "' and ProductType = " + spDTO.ProductType;

        SqlConnection con = DataProvider.TaoKetNoi();
        DataTable dt = DataProvider.LayDataTable(sQuery, con);
        DataProvider.DongKetNoi(con);
        return dt;

    }
    #endregion
}